package com.refusea.train.mapper

import com.refusea.train.domain.entity.LessonConfirmed
import com.refusea.train.domain.entity.LessonSchedule
import com.refusea.train.domain.req.ScheduleQueryReq
import com.refusea.train.domain.vo.LessonScheduleSummary
import org.apache.ibatis.annotations.Delete
import org.apache.ibatis.annotations.Insert
import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Options
import org.apache.ibatis.annotations.Param
import org.apache.ibatis.annotations.Select
import org.apache.ibatis.annotations.Update
import java.time.LocalDate
import java.time.LocalDateTime

@Mapper
interface LessonScheduleMapper {

    @Insert(
        """
        INSERT INTO lesson_schedule (
            school_id, class_group_id, classroom_id, teacher_id, lesson_id, lesson_time
        ) VALUES (
            #{schoolId}, #{classGroupId}, #{classroomId}, #{teacherId}, #{lessonId}, #{lessonTime}
        )
    """
    )
    @Options(useGeneratedKeys = true, keyProperty = "id")
    fun insert(lessonSchedule: LessonSchedule): Int

    @Delete("DELETE FROM lesson_schedule WHERE id = #{id}")
    fun delete(id: Int): Int

    @Update(
        """
        UPDATE lesson_schedule SET 
            school_id = #{schoolId}, class_group_id = #{classGroupId}, classroom_id = #{classroomId}, 
            teacher_id = #{teacherId}, lesson_id = #{lessonId}, lesson_time = #{lessonTime}
        WHERE id = #{id}
    """
    )
    fun update(lessonSchedule: LessonSchedule): Int

    @Select("SELECT * FROM lesson_schedule WHERE id = #{id}")
    fun select(id: Int): LessonSchedule?

    @Select(
        """
        <script>
        SELECT * FROM lesson_schedule where school_id = #{schoolId}
        <if test="classGroupId!= null and classGroupId!= 0">
            and class_group_id = #{classGroupId}
        </if>
        and lesson_time >= #{monday} and lesson_time &lt; #{nextMonday} 
        order by lesson_number
        </script>
        """
    )
    fun list(
        @Param("schoolId") schoolId: Int,
        @Param("classGroupId") classGroupId: Int?,
        @Param("monday") monday: LocalDate,
        @Param("nextMonday") nextMonday: LocalDate,
    ): List<LessonSchedule>

    @Select(
        """
        select class_group_id, any_value(published) from lesson_schedule
        where school_id = #{schoolId} and lesson_time >= #{monday} and lesson_time < #{nextMonday}
        group by class_group_id
        """
    )
    fun summaries(
        @Param("schoolId") schoolId: Int,
        @Param("monday") monday: LocalDate,
        @Param("nextMonday") nextMonday: LocalDate,
    ): List<LessonScheduleSummary>

    @Select(
        """
        SELECT max(lesson_number) FROM lesson_schedule 
        where school_id = #{schoolId} and class_group_id = #{classGroupId} and published=1
        """
    )
    fun maxLessonNumber(@Param("schoolId") schoolId: Int, @Param("classGroupId") classGroupId: Int): Int?

    @Select(
        """
        SELECT id FROM lesson_schedule 
        where school_id = #{schoolId} and class_group_id = #{classGroupId}
        limit 1
        """
    )
    fun firstByClassGroupId(@Param("schoolId") schoolId: Int, @Param("classGroupId") classGroupId: Int): Int?

    @Select(
        """
        select s.lesson_time from lesson_schedule s, class_group g, course c 
        where s.class_group_id=g.id and g.course_id=c.id and 
        s.school_id=#{schoolId} and s.lesson_time >= #{monday} and s.lesson_time < #{nextMonday} 
        and (s.class_group_id!=#{classGroupId} or (s.class_group_id=#{classGroupId} and s.published=1))        
        and (g.classroom_id=#{classroomId} or c.teacher_id=#{teacherId})
        """
    )
    fun occupied(
        @Param("schoolId") schoolId: Int,
        @Param("classGroupId") classGroupId: Int,
        @Param("classroomId") classroomId: Int,
        @Param("teacherId") teacherId: Int,
        @Param("monday") monday: LocalDate, @Param("nextMonday") nextMonday: LocalDate,
    ): List<LocalDateTime>

    @Insert(
        """
        <script>
        insert into lesson_schedule(school_id, class_group_id, lesson_number, lesson_time, published, confirmed)        
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.schoolId}, #{item.classGroupId}, #{item.lessonNumber}, #{item.lessonTime}, #{item.published}, 0)
        </foreach>        
        </script>        
        """
    )
    fun schedule(list: List<LessonSchedule>): Int

    @Delete("DELETE FROM lesson_schedule WHERE class_group_id = #{classGroupId} and published = 0")
    fun deleteUnpublished(classGroupId: Int)

    @Select(
        """
        SELECT s.* FROM lesson_schedule s, class_group g, course c 
        WHERE s.class_group_id=g.id and g.course_id=c.id and 
        s.school_id=#{schoolId} and c.teacher_id=#{teacherId} and s.confirmed=0 and s.published=1
        and s.lesson_time < now()
        """
    )
    fun unconfirmed(
        @Param("schoolId") schoolId: Int,
        @Param("teacherId") teacherId: Int,
    ): List<LessonSchedule>

    @Select(
        """
        SELECT count(1) FROM lesson_schedule s, class_group g, course c 
        WHERE s.class_group_id=g.id and g.course_id=c.id and 
        s.school_id=#{schoolId} and c.teacher_id=#{teacherId} and s.confirmed=0 and s.published=1
        and s.lesson_time < now()
        """
    )
    fun unconfirmedCount(
        @Param("schoolId") schoolId: Int,
        @Param("teacherId") teacherId: Int,
    ): Int

    @Select(
        """
        <script>
        SELECT * FROM lesson_schedule WHERE id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
        </script>        
        """
    )
    fun listByIds(ids: List<Int>): List<LessonSchedule>

    @Update(
        """
        <script>
        update lesson_schedule set confirmed = 1 where id in
        <foreach collection="confirmedList" item="e" separator="," open="(" close=")">
            #{e.scheduleId}
        </foreach>
        and confirmed = 0
        </script>
        """
    )
    fun markConfirmed(confirmedList: List<LessonConfirmed>): Int

    @Select(
        """
        <script>
        SELECT s.* FROM lesson_schedule s, class_group g, course c 
        WHERE s.class_group_id=g.id and g.course_id=c.id and 
        s.school_id = #{schoolId} and s.lesson_time >= #{monday} and s.published=1
        <if test="teacherId!= null and teacherId!= 0">
            and c.teacher_id = #{teacherId}
        </if>
        <if test="classGroupIds!= null and classGroupIds.size > 0">        
            and s.class_group_id in
                <foreach collection="classGroupIds" item="classGroupId" separator="," open="(" close=")">
                    #{classGroupId}
                </foreach>
        </if>
        </script>
        """
    )
    fun schedules(req: ScheduleQueryReq): List<LessonSchedule>


    @Select(
        """
        select * from lesson_schedule 
        where school_id = #{schoolId} and 
        lesson_time >= #{start} and lesson_time < #{end} and 
        published=1 and confirmed=0
        """
    )
    fun recent(
        @Param("schoolId") schoolId: Int,
        @Param("start") start: LocalDateTime,
        @Param("end") end: LocalDate,
    ): List<LessonSchedule>
}
